package com.portal.logic;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

public class FieldDAO {

	//private String ip;
	private Connection connection;

	public FieldDAO(Connection connection) {
		//this.ip = ip;
		this.connection = connection;
	}



	//Create
	public void insert() {
		Field field = null;
		try {
			Object deserializedObject = SerializationUtil.deserialize("c:\\field.out");
			field = (Field) deserializedObject;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement preparedStatement = null;

		try {
			Statement st = connection.createStatement(); 
            st.executeUpdate("INSERT INTO tblFields " + 
                "VALUES ("+field.getId()+", '"+field.getName() +"')"); 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			//ConnectionManager.closeConnection(connection);
		}
	}


/*	public int insert(Field field) {
		PreparedStatement preparedStatement = null;
		int serializedId = -1;
		try {
			String sql_serialize_object = "INSERT INTO serialized_java_objects(object_name, serialized_object) VALUES (?, ?)";
			PreparedStatement pstmt = connection.prepareStatement(sql_serialize_object);

			// just setting the class name
			pstmt.setString(1, field.getClass().getName());
			pstmt.setObject(2, field);
			pstmt.executeUpdate();
			ResultSet rs = pstmt.getGeneratedKeys();

			if (rs.next()) {
				serializedId = rs.getInt(1);
			}
			rs.close();
			pstmt.close();

			//return serialized_id;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			//ConnectionManager.closeConnection(connection);
		}
		return serializedId;
	}
*/

	public int selectMaxId() {
		Statement statement = null;
		ResultSet resultSet = null;
		int id = 0;
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery("SELECT max(id) as max_val FROM tblFields");

			Field field = null;
			while(resultSet.next()){
				id = Integer.parseInt(resultSet.getString("max_val"));
			}

			/*resultSet.close();
			statement.close();*/
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}

		return id;
	}





	public Field selectById(int id) {
		Field field = new Field();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			preparedStatement = connection.prepareStatement("SELECT * FROM tblFields WHERE id = ?");
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				field.setId(resultSet.getInt("id"));
				field.setName(resultSet.getString("name"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}

		return field;
	}

	public Field selectByName(String name) {
		Field field = new Field();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			preparedStatement = connection.prepareStatement("SELECT * FROM tblFields WHERE name = ?");
			preparedStatement.setString(1, name);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				field.setId(resultSet.getInt("id"));
				field.setName(resultSet.getString("name"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}

		return field;
	}

	//Read All
/*	public List<Field> selectAll() {
		PreparedStatement pstmt = null;
		ResultSet resultSet = null;
		List<Field> fields = new LinkedList<Field>();
		//String sql_deserialize_object = "SELECT serialized_object FROM serialized_java_objects WHERE serialized_id = ?";
		String sql_deserialize_object = "SELECT serialized_object FROM serialized_java_objects WHERE object_name = ?";

		try {
			pstmt = connection.prepareStatement(sql_deserialize_object);

			pstmt.setString(1, "com.portal.logic.Field");
			resultSet = pstmt.executeQuery();
			//rs.next();
			Field field = null;
			while (resultSet.next()) {

				byte[] buf = resultSet.getBytes(1);
				ObjectInputStream objectIn = null;
				if (buf != null)
					objectIn = new ObjectInputStream(new ByteArrayInputStream(buf));

				Object deSerializedObject = objectIn.readObject();
				field = (Field)deSerializedObject;

				field.setId(Integer.parseInt(resultSet.getString("id")));
				field.setName(resultSet.getString("name"));
				fields.add(field);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) { 
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
		System.out.println(fields);
		return fields;
	}	*/

		public List<Field> selectAll() {
		Statement statement = null;
		ResultSet resultSet = null;
		List<Field> fields = new LinkedList<Field>();
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery("SELECT * FROM tblFields");

			Field field = null;
			while(resultSet.next()){
				field = new Field();
				field.setId(Integer.parseInt(resultSet.getString("id")));
				field.setName(resultSet.getString("name"));
				fields.add(field);
			}

			resultSet.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
		System.out.println(fields);
		return fields;
	}


	//Update
	public void update() {
		Field field = null;
		try {
			Object deserializedObject = SerializationUtil.deserialize("c:\\editField.out");
			field = (Field) deserializedObject;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement("UPDATE tblFields SET " +
					"name = ? WHERE id = ?");

			preparedStatement.setString(1, field.getName());
			preparedStatement.setInt(2, field.getId());
			preparedStatement.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
	}

	//Delete
	public void delete(int id) {
		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement("DELETE FROM tblFields WHERE id = ?");
			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();


		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
	}
}
